﻿Imports System.IO
Imports MySql.Data.MySqlClient
Public Class frmPRDetail
    'Dim lOldValue As Object
    'Dim lNewValue As Object
    'Dim On_Load As Boolean = True

    Public NewEntry_State As Boolean
    Dim DA As New MySqlDataAdapter
    Dim BDS As New BindingSource
    Dim CMDB As New MySqlCommandBuilder
    Dim Strsql As String
    Dim Mysql As New ClsSQLhelper
    Dim DSWHIS As New DSWHIS
    Dim MyWhis As New cls_whisframework


#Region "Function"
    Sub Load_Budget()
        Try
            With Me.CboYearBudget
                Dim DTBudget As New DataTable
                Strsql = "select bdg_year from yearbudget order by bdg_year desc "
                DTBudget = Mysql.GetMYSQLDataTable(Strsql, "yearbudget")
                .DataSource = DTBudget
                .DisplayMember = "bdg_year"
                .ValueMember = "bdg_year"
                'DTBudget.Dispose()
                .SelectedValue = MyWhis.Get_CurrentYearbudget(ThaiFormat:=True)
            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Budget"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub
    Sub Load_Site()
        Try
            With Me.CboSite
                Dim DTSite As New DataTable
                Strsql = "Select siteid,site from site order by site"
                DTSite = Mysql.GetMYSQLDataTable(Strsql, "site")
                .DataSource = DTSite
                .DisplayMember = "site"
                .ValueMember = "siteid"
                'DTSite.Dispose()
                .SelectedValue = My.Settings.CurrentSite
                '.SelectionStart = 0
            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Budget"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub
    Sub Load_CboDepartment()
        Try
            Dim DTdepartment As New DataTable
            Strsql = "Select depcode,depname from departments order by depcode"
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            DTdepartment = Mysql.GetMYSQLDataTable(Strsql, "departments")
            With cboDepartment
                .DataSource = DTdepartment
                .DisplayMember = "depname"
                .ValueMember = "depcode"
                .SelectionStart = 0

            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_CboDepartment"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

        'DTdepartment.Dispose()
    End Sub

    Sub Load_Orderstatus()
        Try
            Dim DTOrderstatus As New DataTable
            Strsql = "Select * from orderstatus where statustype = 'pr'"
            DTOrderstatus = Mysql.GetMYSQLDataTable(Strsql, "orderstatus")
            With Me.CboOrderStatus
                .DataSource = DTOrderstatus
                .DisplayMember = "orderstatus"
                .ValueMember = "orderstatusid"
            End With

        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Orderstatus"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub
    Function Validate_Data() As Boolean
        Try
            Validate_Data = False
            If Me.GridOrderdetail.RowCount = 1 Then
                MsgBox("กรุณาเลือกรายการที่จะขอเสนอซื้อ", MsgBoxStyle.Exclamation, "ไม่สามารถบรรทึกข้อมูลได้")
                Exit Function
            End If
            If Me.TxtOrderNumber.Text = "" Then
                MsgBox("กรุณาใส่รหัสใบสั่งซื้อ", MsgBoxStyle.Exclamation, "ไม่สามารถบรรทึกข้อมูลได้")
                Exit Function
            End If
            If Me.CboYearBudget.SelectedValue = -1 Then
                MsgBox("กรุณาใส่ปีงบประมาณ", MsgBoxStyle.Exclamation, "ไม่สามารถบรรทึกข้อมูลได้")
                Exit Function
            End If

            If NewEntry_State = True Then
                Strsql = "select Count(*) as rowcount from prorder where ordernumber = '" & Me.TxtOrderNumber.Text & "'  and site = " & Me.CboSite.SelectedValue
                If Mysql.MySQLExecuteScalar(Strsql) > 0 Then
                    MsgBox("เลขที่นี้มีอยู่แล้วไม่สามารถใช้ได้ กรุณาใส่เลขที่ใหม่", MsgBoxStyle.Exclamation, "ไม่สามารถบรรทึกข้อมูลได้")
                    Exit Function
                End If
            End If
            Validate_Data = True
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Validate_Data"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Function
    Sub Load_Data()
        Try
            Dim DS_PRdetail As New DataSet
            Dim DS_PROrder As New DataSet

            Dim DT_Vendor As New DataTable
            Dim MyWhis As New cls_whisframework


            Me.TxtOrderedBy.Text = MyWhis.Get_CurrentUserfullname


            Dim Col_Vendor As New DataGridViewComboBoxColumn
            Dim Col_Products As New DataGridViewTextBoxColumn
            Dim Col_Uom As New DataGridViewTextBoxColumn
            Dim Col_OrderQty As New DataGridViewTextBoxColumn
            Dim Col_OnHandQty As New DataGridViewTextBoxColumn
            Dim Col_Drawqty As New DataGridViewTextBoxColumn
            Dim Col_Price As New DataGridViewTextBoxColumn
            Dim Col_Note As New DataGridViewTextBoxColumn
            Dim Col_Totalprice As New DataGridViewTextBoxColumn
            Dim Col_OrderNumber As New DataGridViewTextBoxColumn
            Dim Col_ProductID As New DataGridViewTextBoxColumn

            Dim i As Integer = 0


            With Col_OrderNumber
                .Visible = False
                .DataPropertyName = "ordernumber"
            End With

            With Col_Vendor
                Strsql = "select vendorproducts.vendorid,vendorcompany.companyname  from vendorproducts  left join vendorcompany on vendorproducts.vendorid= vendorcompany.companyid group by companyname order by companyname"
                DT_Vendor = Mysql.GetMYSQLDataTable(Strsql, "vendorcompany")
                .DataSource = DT_Vendor
                .DisplayMember = "companyname"
                .ValueMember = "vendorid"
                .HeaderText = "บริษัท"
                .Width = 190
                .DataPropertyName = "vendorid"
            End With

            With CboVender
                Strsql = "select vendorproducts.vendorid,vendorcompany.companyname  from vendorproducts  left join vendorcompany on vendorproducts.vendorid= vendorcompany.companyid group by companyname order by companyname"
                DT_Vendor = Mysql.GetMYSQLDataTable(Strsql, "vendorcompany")
                .DataSource = DT_Vendor
                .DisplayMember = "companyname"
                .ValueMember = "vendorid"

                '.HeaderText = "บริษัท"
                '.Width = 190
                '.DataPropertyName = "vendorid"
            End With

            With Col_ProductID
                .Visible = True
                .HeaderText = "รหัสสินค้า"
                .DataPropertyName = "itemnumber"
            End With

            With Col_Products
                .HeaderText = "ชื่อสินค้า"
                .Width = 300

            End With
            'Strsql = "select item.itemnumber,concat(item.tradename,' ',item.itemname) as itemname from siteitem left join item on siteitem.itemnumber = item.itemnumber left join itemcontainunit on  siteitem.itemnumber = itemcontainunit.itemid where siteitem.site = " & Me.CboSite.SelectedValue & " group by  item.itemnumber   order by itemname"
            'Strsql = "select concat(vendor_product_band,' ',item.itemname) as itemname,vendor_product_id as itemnumber  from vendorproducts left join item on vendor_product_id = item.itemnumber "
            'DT_Item = Mysql.GetMYSQLDataTable(Strsql, "item")
            'With Col_Products
            '    .DataSource = DT_Item
            '    .DisplayMember = "itemname"
            '    .ValueMember = "itemnumber"
            '    .HeaderText = "ชื่อสินค้า"
            '    .Width = 300
            '    .DataPropertyName = "itemnumber"
            'End With

            With Col_OrderQty
                .HeaderText = "จำนวน"
                .DefaultCellStyle.NullValue = "1"
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                .DataPropertyName = "orderedqty"
            End With

            With Col_Uom
                .HeaderText = "หน่วยบรรจุ"
                .Width = 100
                .DataPropertyName = "containunit"
            End With

            With Col_OnHandQty
                .HeaderText = "ในสต็อก"
                .Width = 80
                .DefaultCellStyle.Format = "N0"
                .DefaultCellStyle.NullValue = "0"
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                .DataPropertyName = "onhandqty"
                .ReadOnly = True
            End With

            With Col_Price
                .HeaderText = "ราคากลาง"
                .Width = 80
                .DefaultCellStyle.Format = "N0"
                .DefaultCellStyle.NullValue = "0"
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                .DataPropertyName = "priceperitem"
                .ReadOnly = True
            End With


            With Col_Totalprice
                .HeaderText = "รวมมูลค่า"
                .Width = 80
                .DefaultCellStyle.Format = "N0"
                .DefaultCellStyle.NullValue = "0"
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                .DataPropertyName = "totalprice"
                .ReadOnly = True
            End With


            With Col_Note
                .HeaderText = "หมายเหตุ"
                .Width = 100
                .DataPropertyName = "note"
            End With



            With GridOrderdetail

                .AutoGenerateColumns = False
                .Columns.Add(Col_Vendor)
                .Columns.Add(Col_Products)
                .Columns.Add(Col_OrderQty)
                .Columns.Add(Col_Uom)
                .Columns.Add(Col_OnHandQty)
                .Columns.Add(Col_Price)
                .Columns.Add(Col_Totalprice)
                .Columns.Add(Col_Note)
                .Columns.Add(Col_OrderNumber)
                .Columns.Add(Col_ProductID)

                Select Case NewEntry_State
                    Case True

                    Case False

                        Dim DTPROrder As New DataTable
                        Strsql = "Select ordernumber,orderdate,orderstatus,yearbudget,site,department,orderedby,producttype,reason from prorder where ordernumber = " & Me.TxtOrderNumber.Text
                        Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                        DTPROrder = Mysql.GetMYSQLDataTable(Strsql, "prorder")

                        ' Me.TxtOrderNumber.Text = DTPROrder.Rows(0).Item("ordernumber").ToString
                        Me.TxtOrderedBy.Text = DTPROrder.Rows(0).Item("orderedby").ToString
                        Me.TxtProductType.Text = DTPROrder.Rows(0).Item("producttype").ToString
                        Me.TxtReason.Text = DTPROrder.Rows(0).Item("reason").ToString
                        Me.DTPOrderedDate.Value = DTPROrder.Rows(0).Item("orderdate")
                        Me.CboSite.SelectedValue = DTPROrder.Rows(0).Item("site")
                        Me.cboDepartment.SelectedValue = DTPROrder.Rows(0).Item("department")
                        Me.CboYearBudget.SelectedValue = DTPROrder.Rows(0).Item("yearbudget")
                        Me.CboOrderStatus.SelectedValue = DTPROrder.Rows(0).Item("orderstatus")

                        Exit Select

                        Strsql = "Select * from prdetail where ordernumber = '" & Me.TxtOrderNumber.Text & "'"
                        Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                        DA = New MySqlDataAdapter(Strsql, ClsSQLhelper.MySQLConnectionStr)
                        DA.Fill(DSWHIS, "prdetail")
                        '  BDS = New BindingSource(DSWHIS.Tables("prdetail"), "prdetail")
                        CMDB = New MySqlCommandBuilder(DA)
                        .DataSource = DSWHIS.Tables("prdetail")
                        Me.BtnCancel.Enabled = True
                        If Me.CboOrderStatus.SelectedValue = 3 Then
                            Enable_Control(False)
                            ' MsgBox("รายการนี้โดนยกเลิกไปแล้ว", MsgBoxStyle.Exclamation)
                        End If
                End Select



            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Data"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try


    End Sub
    Sub Enable_Control(ByVal Enable As Boolean)
        Try
            Me.TxtOrderNumber.Enabled = Enable
            Me.TxtOrderedBy.Enabled = Enable
            Me.TxtProductType.Enabled = Enable
            Me.TxtReason.Enabled = Enable
            Me.cboDepartment.Enabled = Enable
            Me.CboOrderStatus.Enabled = Enable
            Me.CboSite.Enabled = Enable
            Me.CboYearBudget.Enabled = Enable
            Me.DTPOrderedDate.Enabled = Enable
            Me.GridOrderdetail.Enabled = Enable
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Data"
                .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub
#End Region

    Private Sub frmPRDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Load_Budget()
        Load_Site()
        Load_CboDepartment()
        Load_Orderstatus()
        Load_Data()

    End Sub
    Private Sub frmPRDetail_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
        Strsql = "Update prorder set Lockedby = '',lockeddatetime = null,lockedbycomputer = '' where ordernumber = " & Me.TxtOrderNumber.Text
        Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
        Mysql.MySQLExecute(Strsql)
        frmPROrder.Load_Data()
        frmPROrder.WindowState = FormWindowState.Maximized
        frmPROrder.Show()

    End Sub




    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        '  Try
        'validate
        If Validate_Data() = False Then Exit Sub

        If (MsgBox("คุณต้องการบันทึกข้อมูลใช่หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยันการบันทึกข้อมูล")) = MsgBoxResult.No Then Exit Sub

        Select Case NewEntry_State
            Case True
                Try
                    Strsql = "Insert into prorder (ordernumber,site,department,orderdate,orderedby,yearbudget,lockedby,lockeddatetime,producttype,reason,createdby,createddatetime,orderstatus ) "
                    Strsql = Strsql & " values (" & Me.TxtOrderNumber.Text & "," & Me.CboSite.SelectedValue & "," & Me.cboDepartment.SelectedValue & ",'" & Pn_Framework.MysqlDateTimeFormat(Me.DTPOrderedDate.Value) & "','" & Me.TxtOrderedBy.Text & "'"
                    Strsql = Strsql & ",'" & Me.CboYearBudget.SelectedValue & "','" & My.Settings.Currentuser & "','" & Pn_Framework.MysqlDateTimeFormat(Now) & "','" & Me.TxtProductType.Text & "','" & Me.TxtReason.Text & "','" & My.Settings.Currentuser & "','" & Pn_Framework.MysqlDateTimeFormat(Now) & "'," & Me.CboOrderStatus.SelectedValue & ")"
                    Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                    Mysql.MySQLExecute(Strsql)
                    With Me.GridOrderdetail
                        For i As Integer = 0 To .RowCount - 2

                            Strsql = "Insert into prdetail (recordnumber,itemnumber,orderedqty,containunit,onhandqty,priceperitem,totalprice,note,ordernumber)"
                            Strsql = Strsql & " Values (" & i + 1 & "," & .Rows(i).Cells(0).Value & "," & IIf(.Rows(i).Cells(2).Value Is Nothing, 1, .Rows(i).Cells(2).Value)
                            Strsql = Strsql & ",'" & .Rows(i).Cells(3).Value
                            Strsql = Strsql & "'," & IIf(.Rows(i).Cells(4).Value Is Nothing, 0, .Rows(i).Cells(2).Value)
                            Strsql = Strsql & "," & .Rows(i).Cells(5).Value
                            Strsql = Strsql & "," & ((IIf(.Rows(i).Cells(5).Value Is Nothing, 0, .Rows(i).Cells(5).Value)) * (IIf(.Rows(i).Cells(2).Value Is Nothing, 1, .Rows(i).Cells(2).Value)))
                            Strsql = Strsql & ",'" & IIf(.Rows(i).Cells(7).Value Is Nothing, "", .Rows(i).Cells(7).Value) & "','" & Me.TxtOrderNumber.Text.Trim & "')"
                            If Mysql.MySQLExecute(Strsql) = -1 Then


                                With frmDebug
                                    .lblFormName.Text = Me.Name
                                    .lblFunctionName.Text = "Load_Data"
                                    .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                                    .MemoSQL.Text = Strsql
                                    .ShowDialog()
                                End With
                            Else
                                NewEntry_State = False
                                Me.TxtOrderNumber.ReadOnly = True
                                Me.BtnCancel.Enabled = True
                                MsgBox("เพิ่มข้อมูลเสร็จเรียบร้อยแล้ว")
                            End If

                   
                    Next
                End With
                Catch ex As Exception
                    With frmDebug
                        .lblFormName.Text = Me.Name
                        .lblFunctionName.Text = "BtnSave_Click"
                        .MemoErr_Description.Text = "กรุณาตรวจสอบคำสั่ง sql"
                        .MemoSQL.Text = Strsql
                        .ShowDialog()
                    End With
                End Try
            Case False
                Try
                    Strsql = "Update prorder set  site =  " & Me.CboSite.SelectedValue & " , department = " & Me.cboDepartment.SelectedValue & " , orderdate = '" & Pn_Framework.MysqlDateTimeFormat(Me.DTPOrderedDate.Value) & "', orderedby = '" & Me.TxtOrderedBy.Text & "' ,Updatedby = '" & My.Settings.Currentuser & "' , updateddatetime = '" & Pn_Framework.MysqlDateTimeFormat(Me.DTPOrderedDate.Value) & "',producttype = '" & Me.TxtProductType.Text & "',reason = '" & Me.TxtReason.Text & "'"
                    Strsql = Strsql & " where ordernumber =  " & Me.TxtOrderNumber.Text
                    Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                    Mysql.MySQLExecute(Strsql)
                    DA.Update(DSWHIS, "prdetail")

                Catch ex As Exception

                End Try

                Try
                    If Me.CboOrderStatus.SelectedValue = 3 Then
                        Strsql = "update prorder set orderstatus = 3,canceltype = " & DlgCancelPROrder.CboCancelType.SelectedValue & ",cancelnote = '" & DlgCancelPROrder.TxtCancelNote.Text & "',canceldatetime = '" & Pn_Framework.MysqlDateTimeFormat(DlgCancelPROrder.DTPCancelDate.Value, True) & "',cancelby = '" & DlgCancelPROrder.TxtCanceluser.Text & "' where ordernumber = '" & DlgCancelPROrder.TxtPrOrder.Text & "'"
                        Mysql.MySQLExecute(Strsql)
                    End If
                Catch ex As Exception

                End Try



                MsgBox("ปรับปรุงข้อมูลเสร็จเรียบร้อยแล้ว")
        End Select


      




    End Sub

    Private Sub TxtPrNumber_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtOrderNumber.TextChanged
        Try

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

    Private Sub GridOrderdetail_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles GridOrderdetail.CellBeginEdit
        Try
            If Me.TxtOrderNumber.Text = "" Then
                MsgBox("กรุณาใส่เลขที่ใบสั่งซื้อก่อนครับ", MsgBoxStyle.Exclamation)
                e.Cancel = True
                Me.TxtOrderNumber.Focus()
                Exit Sub
            End If
            If Me.NewEntry_State = False Then Exit Sub
            Strsql = "select Count(*) as rowcount from prorder where ordernumber = '" & Me.TxtOrderNumber.Text & "'  and site = " & Me.CboSite.SelectedValue

            If Mysql.MySQLExecuteScalar(Strsql) > 0 Then

                MsgBox("เลขที่นี้มีอยู่แล้วไม่สามารถใช้ได้ กรุณาใส่เลขที่ใหม่", MsgBoxStyle.Exclamation, "ไม่สามารถบรรทึกข้อมูลได้")
                e.Cancel = True
                Exit Sub
            End If
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_CellBeginEdit"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub

    Private Sub GridOrderdetail_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridOrderdetail.CellClick
        Try
            With Me.GridOrderdetail
                .Rows(.CurrentCell.RowIndex).Cells(8).Value = Me.TxtOrderNumber.Text
            End With
        Catch ex As Exception

            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_CellClick"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

     
    End Sub

    Private Sub GridOrderdetail_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridOrderdetail.CellContentClick

    End Sub

    Private Sub BtnLock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            If MsgBox("คุณต้องการล็อค Order นี้เพื่อไม่ให้มีการแก้ไขภายหลังใช่หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "สั่งห้ามแก้ไข") = MsgBoxResult.No Then Exit Sub
            Me.GridOrderdetail.Enabled = False
            Me.BtnDelete.Enabled = False
            Me.BtnSave.Enabled = False
            Me.cboDepartment.Enabled = False
            Me.DTPOrderedDate.Enabled = False


            MsgBox("Order นี้ถูกล็อกเรียบร้อยแล้ว")
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnLock_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

        Try
            Strsql = "Update prorder set orderstatus = 2 where ordernumber = " & TxtOrderNumber.Text
            Mysql.MySQLExecute(Strsql)
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnLock_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

    End Sub

   

    Private Sub BtnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrint.Click
        Try
            Select Case Me.TxtOrderNumber.Text
                Case ""
                    MsgBox("กรุณาบันทึกข้อมูลก่อน", MsgBoxStyle.Exclamation)
                    BtnSave_Click(sender, e)
                Case Is <> ""
                    Dim DSTemp As New DataSet
                    Strsql = "Select  prdetail.ordernumber ,prorder.orderdate,itemrownumber,itemname,drawqty,onhandqty,orderedqty,IFNULL(priceperitem,0) as priceperitem,priceperitem*onhandqty as  totalprice,ifnull(note,'') as note ,department.depname,ifnull(prorder.reason,'') as reason ,ifnull(prorder.producttype,'') as producttype  from prdetail "
                    Strsql = Strsql & " left join item on  left(itemcode,5) = item.itemnumber "
                    Strsql = Strsql & " Left Join prorder ON prorder.ordernumber = prdetail.ordernumber"
                    Strsql = Strsql & " Left join department on prorder.department = department.depcode"
                    Strsql = Strsql & " where prdetail.ordernumber  = " & Me.TxtOrderNumber.Text & " order by itemrownumber"
                    Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
                    DSTemp = Mysql.GetMYSQLDataset(Strsql, "DSPRDetail", "prdetail")
                    Dim myfile As New FileInfo("C:\PROrder.xml")
                    If myfile.Exists = True Then myfile.Attributes = FileAttributes.Normal
                    DSTemp.WriteXml("C:\PROrder.xml", XmlWriteMode.WriteSchema)
                    myfile.Attributes = FileAttributes.Hidden

                    With frmRptProrder
                        .MdiParent = frmmain
                        .Show()
                    End With
            End Select
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnPrint_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try


    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        Try
            If (MsgBox("คุณต้องการลบ Order " & Me.TxtOrderNumber.Text & " นี้ใช่หรือไม่ ?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "ยืนยันการลบ") = MsgBoxResult.No) Then Exit Sub
            Strsql = "Delete from prdetail where ordernumber = '" & Me.TxtOrderNumber.Text & "'"
            Mysql.MySQLExecute(Strsql)


        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnDelete_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try

        Try
            Strsql = "Delete from prorder where ordernumber = " & Me.TxtOrderNumber.Text
            Mysql.MySQLExecute(Strsql)
            MsgBox("ลบข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
            Me.Dispose()
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnDelete_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
        
    End Sub

    Private Sub GridOrderdetail_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridOrderdetail.CellEndEdit
        Try
            With GridOrderdetail
                If e.ColumnIndex <> 3 Then Exit Sub
                Dim IntPricePerUnit As Integer = 0
                'Strsql = "Select qtyperunit from itemcontainunit where  itemid = " & GridOrderdetail.Rows(.CurrentCell.RowIndex).Cells(9).Value & " and containunit = '" & GridOrderdetail.Rows(.CurrentCell.RowIndex).Cells(3).Value & "'"
                'IntPricePerUnit = Mysql.MySQLExecuteScalar(Strsql)
                .Rows(.CurrentCell.RowIndex).Cells(6).Value = CType(.Rows(.CurrentCell.RowIndex).Cells(2).Value, Integer) * CType(.Rows(.CurrentCell.RowIndex).Cells(5).Value, Integer)

            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_CellEndEdit"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
       
        'If Me.GridOrderdetail.Rows.Count = 0 Then Exit Sub
        'If On_Load = True Then Exit Sub
        'Me.lNewValue = GridOrderdetail.CurrentCell.Value
        'Me.GridOrderdetail.Rows(e.RowIndex).Cells(5).Value = Val(If(Me.GridOrderdetail.Rows(e.RowIndex).Cells(1).Value Is Nothing, 1, Me.GridOrderdetail.Rows(e.RowIndex).Cells(1).Value)) * Val(Me.GridOrderdetail.Rows(e.RowIndex).Cells(4).Value)
        'Me.GridOrderdetail.Rows(e.RowIndex).Cells(3).Value = Val(If(Me.GridOrderdetail.Rows(e.RowIndex).Cells(2).Value Is Nothing, 0, Me.GridOrderdetail.Rows(e.RowIndex).Cells(2).Value)) - Val(If(Me.GridOrderdetail.Rows(e.RowIndex).Cells(1).Value Is Nothing, 1, Me.GridOrderdetail.Rows(e.RowIndex).Cells(1).Value))

    End Sub

    Private Sub GridOrderdetail_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridOrderdetail.CellValueChanged
        Try
            If e.ColumnIndex <> 1 Then Exit Sub
            With GridOrderdetail
                Try
                    ' .Rows(.CurrentCell.RowIndex).Cells(4).Value = CType(.Rows(.CurrentCell.RowIndex).Cells(3).Value, Integer) - CType(.Rows(.CurrentCell.RowIndex).Cells(1).Value, Integer)
                    .Rows(.CurrentCell.RowIndex).Cells(5).Value = CType(.Rows(.CurrentCell.RowIndex).Cells(2).Value, Integer) * CType(.Rows(.CurrentCell.RowIndex).Cells(5).Value, Integer)

                Catch ex As Exception
                    MsgBox(Err.Number & " : " & ex.Message)
                End Try


            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_CurrentCellDirtyStateChanged"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
      
        'If GridOrderdetail.Rows(e.RowIndex).Cells(0).Value = Nothing Then
        '    MsgBox("กรุณาเลือกรายการก่อน", MsgBoxStyle.Exclamation)
        '    If e.RowIndex <> GridOrderdetail.NewRowIndex Then
        '        GridOrderdetail.AllowUserToAddRows = False
        '    End If

        '    Exit Sub
        'Else
        '    GridOrderdetail.AllowUserToAddRows = True
        'End If

        'If GridOrderdetail.Rows(e.RowIndex).Cells(1).Value = Nothing Then
        '    MsgBox("กรุณาใส่จำนวน", MsgBoxStyle.Exclamation)
        '    GridOrderdetail.AllowUserToAddRows = False
        '    Exit Sub
        'Else
        '    GridOrderdetail.AllowUserToAddRows = True
        'End If

        ''If GridOrderdetail.Rows(e.RowIndex).Cells(3).Value = Nothing Then
        ''    MsgBox("กรุณาใส่ราคา", MsgBoxStyle.Exclamation)
        ''    GridOrderdetail.AllowUserToAddRows = False
        ''    Exit Sub
        ''Else
        ''    GridOrderdetail.AllowUserToAddRows = True
        ''End If

        'If e.ColumnIndex = 1 Then
        '    If IsNumeric(GridOrderdetail.Rows(e.RowIndex).Cells(1).Value) = False Then
        '        MsgBox("ระบุเป็นตัวเลขเท่านั้น", MsgBoxStyle.Exclamation)
        '        GridOrderdetail.Rows(e.RowIndex).Cells(1).Value = lOldValue
        '    End If
        'End If
        ''If e.ColumnIndex = 2 Then
        ''    If IsNumeric(GridOrderdetail.Rows(e.RowIndex).Cells(2).Value) = False Then
        ''        GridOrderdetail.Rows(e.RowIndex).Cells(2).Value = lOldValue
        ''        MsgBox("ระบุเป็นตัวเลขเท่านั้น", MsgBoxStyle.Exclamation)
        ''    End If
        ''End If
        'If e.ColumnIndex = 4 Then
        '    If IsNumeric(GridOrderdetail.Rows(e.RowIndex).Cells(4).Value) = False Then
        '        MsgBox("ระบุเป็นตัวเลขเท่านั้น", MsgBoxStyle.Exclamation)
        '        GridOrderdetail.Rows(e.RowIndex).Cells(4).Value = lOldValue
        '    End If
        'End If
    End Sub

    Private Sub GridOrderdetail_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridOrderdetail.CurrentCellDirtyStateChanged
        Try
            Dim IntStandardPrice As Integer = 0
            With GridOrderdetail
                If .IsCurrentCellDirty = False Then Return

                .CommitEdit(DataGridViewDataErrorContexts.Commit)

                If .CurrentCell.ColumnIndex = 0 Then
                    Dim Col_Products As New DataGridViewComboBoxCell
                    Dim DT_Item As New DataTable

                    'Strsql = "select item.itemnumber,concat(item.tradename,' ',item.itemname) as itemname from siteitem left join item on siteitem.itemnumber = item.itemnumber left join itemcontainunit on  siteitem.itemnumber = itemcontainunit.itemid where siteitem.site = " & Me.CboSite.SelectedValue & " group by  item.itemnumber   order by itemname"
                    Strsql = "select item.itemname,vendor_product_id from vendorproducts left join item on vendor_product_id = item.itemnumber  where  vendorid = " & .Rows(.CurrentCell.RowIndex).Cells(0).Value & " group by vendor_product_band,item.itemname  order by vendor_product_band,item.itemname "
                    DT_Item = Mysql.GetMYSQLDataTable(Strsql, "item")
                    With Col_Products
                        For i As Integer = 0 To DT_Item.Rows.Count - 1
                            .Items.Add(DT_Item.Rows(i).Item(0).ToString)
                        Next

                    End With
                    .Rows(.CurrentCell.RowIndex).Cells(1) = Col_Products
                    .Rows(.CurrentCell.RowIndex).Cells(1).Value = DT_Item.Rows(0).Item(0).ToString


                    Dim colUnit As New DataGridViewComboBoxCell
                    Strsql = "select vendorproducts.vendor_product_containunit  from vendorproducts   where vendor_product_id='" & DT_Item.Rows(0).Item(1).ToString & "' and vendorid = " & .Rows(.CurrentCell.RowIndex).Cells(0).Value
                    Dim DTUnit As New DataTable
                    DTUnit = Mysql.GetMYSQLDataTable(Strsql, "vendorproducts")
                    colUnit.Items.Clear()
                    For i As Integer = 0 To DTUnit.Rows.Count - 1
                        colUnit.Items.Add(DTUnit.Rows(i).Item(0).ToString)
                    Next
                    .Rows(.CurrentCell.RowIndex).Cells(3) = colUnit
                    .Rows(.CurrentCell.RowIndex).Cells(3).Value = DTUnit.Rows(0).Item(0).ToString
                    .Rows(.CurrentCell.RowIndex).Cells(9).Value = DT_Item.Rows(0).Item(1).ToString
                    Strsql = "Select vendor_product_price  from vendorproducts where vendor_product_id = " & GridOrderdetail.Rows(.CurrentCell.RowIndex).Cells(9).Value
                    IntStandardPrice = Mysql.MySQLExecuteScalar(Strsql)

                    .Rows(.CurrentCell.RowIndex).Cells(5).Value = IntStandardPrice
                    .Rows(.CurrentCell.RowIndex).Cells(6).Value = CType(.Rows(.CurrentCell.RowIndex).Cells(2).Value, Integer) * CType(.Rows(.CurrentCell.RowIndex).Cells(5).Value, Integer)
                End If

                If .CurrentCell.ColumnIndex = 1 Then
                    Strsql = "select vendor_product_id  from vendorproducts  left join item on vendorproducts.vendor_product_id= item.itemnumber where item.itemname='" & .Rows(.CurrentCell.RowIndex).Cells(1).Value & "' and vendorid = " & .Rows(.CurrentCell.RowIndex).Cells(0).Value
                    .Rows(.CurrentCell.RowIndex).Cells(9).Value = Mysql.MySQLExecuteScalar(Strsql)

                    Dim colUnit As New DataGridViewComboBoxCell
                    Strsql = "select vendorproducts.vendor_product_containunit  from vendorproducts  left join item on vendorproducts.vendor_product_id= item.itemnumber where item.itemname='" & .Rows(.CurrentCell.RowIndex).Cells(1).Value & "' and vendorid = " & .Rows(.CurrentCell.RowIndex).Cells(0).Value


                    Dim DTUnit As New DataTable
                    DTUnit = Mysql.GetMYSQLDataTable(Strsql, "vendorproducts")
                    colUnit.Items.Clear()
                    For i As Integer = 0 To DTUnit.Rows.Count - 1
                        colUnit.Items.Add(DTUnit.Rows(i).Item(0).ToString)
                    Next
                    .Rows(.CurrentCell.RowIndex).Cells(3) = colUnit
                    .Rows(.CurrentCell.RowIndex).Cells(3).Value = DTUnit.Rows(0).Item(0).ToString
                    Dim DTITEM As New DataTable
                    Strsql = "Select onhandqty from inventory where itemnumber = " & GridOrderdetail.Rows(.CurrentCell.RowIndex).Cells(9).Value & " and site = " & Me.CboSite.SelectedValue
                    If Mysql.MySQLExecuteScalar(Strsql) = Nothing Then
                        .Rows(.CurrentCell.RowIndex).Cells(4).Value = 0
                    Else
                        .Rows(.CurrentCell.RowIndex).Cells(4).Value = Mysql.MySQLExecuteScalar(Strsql)
                    End If
                    Dim intNeedMore As Integer = CType(.Rows(.CurrentCell.RowIndex).Cells(4).Value, Integer) - CType(.Rows(.CurrentCell.RowIndex).Cells(2).Value, Integer)
                    'If intNeedMore >= 0 Then
                    '    .Rows(.CurrentCell.RowIndex).Cells(4).Style.ForeColor = Color.Black
                    '    .Rows(.CurrentCell.RowIndex).Cells(4).Value = 0
                    'Else
                    '    .Rows(.CurrentCell.RowIndex).Cells(4).Value = Math.Abs(intNeedMore)
                    '    .Rows(.CurrentCell.RowIndex).Cells(4).Style.ForeColor = Color.Red
                    'End If



                    Strsql = "Select vendor_product_price  from vendorproducts where vendor_product_id = " & GridOrderdetail.Rows(.CurrentCell.RowIndex).Cells(9).Value
                    IntStandardPrice = Mysql.MySQLExecuteScalar(Strsql)

                    .Rows(.CurrentCell.RowIndex).Cells(5).Value = IntStandardPrice
                    .Rows(.CurrentCell.RowIndex).Cells(6).Value = CType(.Rows(.CurrentCell.RowIndex).Cells(2).Value, Integer) * CType(.Rows(.CurrentCell.RowIndex).Cells(5).Value, Integer)
                    'DTITEM = Mysql.GetMYSQLDa8taTable(Strsql, "itemcontainunit")

                End If
            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_CurrentCellDirtyStateChanged"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
    End Sub

    Private Sub GridOrderdetail_DefaultValuesNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles GridOrderdetail.DefaultValuesNeeded
        Try
            With Me.GridOrderdetail
                .Rows(e.Row.Index).Cells(8).Value = Me.TxtOrderNumber.Text
            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "GridOrderdetail_DefaultValuesNeeded"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
      
    End Sub

    Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
        Try
            Strsql = "Update prorder set Lockedby = '',lockeddatetime = null,lockedbycomputer = '' where ordernumber = " & Me.TxtOrderNumber.Text
            Me.EventLog1.WriteEntry(Me.Name & " : " & Strsql, EventLogEntryType.Information, 1)
            Mysql.MySQLExecute(Strsql)
            Me.Dispose()
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnClose_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try
       
    End Sub

    Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
        Try
            If Me.NewEntry_State = True Then Exit Sub
            Select Case CboOrderStatus.SelectedValue
                Case 3
                    DlgCancelPROrder.TxtPrOrder.Text = Me.TxtOrderNumber.Text

                    If DlgCancelPROrder.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub
                    Me.GridOrderdetail.ReadOnly = True

                Case Else
                    Strsql = "Select ponumber from prorder where ordernumber = '" & Me.TxtOrderNumber.Text & "'"
                    If Mysql.MySQLExecuteScalar(Strsql).ToString <> "" Then
                        MsgBox("รายการนี้ถูกใช้ในการออกใบสั่งซื้อแล้วจึงไม่สามารถยกเลิกรายการได้ครับ", MsgBoxStyle.Critical)
                        Exit Sub
                    End If

                    DlgCancelPROrder.TxtPrOrder.Text = Me.TxtOrderNumber.Text
                    DlgCancelPROrder.TxtCanceluser.Text = My.Settings.Currentuser
                    DlgCancelPROrder.DTPCancelDate.Value = Now
                    If DlgCancelPROrder.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub
                    Me.GridOrderdetail.ReadOnly = True
                    Me.CboOrderStatus.SelectedValue = 3
                    Enable_Control(False)
            End Select

        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "BtnCancel_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = Strsql
                .ShowDialog()
            End With
        End Try




    End Sub
End Class